package org.ezplatform.office.questionnaire.dao;
 
import java.util.Date;
import java.util.List;

import org.ezplatform.core.dao.jpa.JpaBaseDao;
import org.ezplatform.office.questionnaire.entity.Questionnaire;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;


@Repository("questionnaireDao")
public interface QuestionnaireDao extends JpaBaseDao<Questionnaire, String>{
	
	/*根据名称获取问卷信息*/
	@Query("from Questionnaire  where   name = ?1 and corpId=?2 ")
	Questionnaire getQuestionnaireByName(String name, String corpId);
	
	@Modifying
	@Query(nativeQuery = true, value = "update  oa_questionnaire set status='1' where  status ='2' and start_date <= SYSDATE() and end_date >= SYSDATE()")
	int updateSetOpenStatus();
	
	@Modifying
	@Query(nativeQuery = true, value = "update  oa_questionnaire set status='0' where  status in ('1','2') and end_date <= SYSDATE()")
	int updateSetCloseStatus();
	
	/*根据根据问卷适用范围获取问卷信息*/
	@Query("from Questionnaire  where   questionnaireScope in (?1) and corpId=?2 and status =?3  ORDER BY createdDate DESC")
	List<Questionnaire> getQuestionByQuesScope(List<String> scope, String corpId, String status);
	
	/**
	 * 根据根据问卷适用范围获取问卷id  查询已发布的问卷
	 * */
	@Query("select id from Questionnaire  where   questionnaireScope in (?1) and corpId=?2 and publish =?3  ORDER BY createdDate DESC")
	List<String> getQuestionIdsByQuesScope(List<String> scope, String corpId, int publish);
	
	/**
	 * 查询答卷范围默认所有的问卷
	 * @param corpId
	 * @param publish
	 * @return
	 */
	@Query("select id from Questionnaire where questionnaireScope is null and corpId=?1 and publish =?2 ORDER BY createdDate DESC")
	List<String> getQuestionIdsByQuesScopeIsNull(String corpId, int publish);
	
	/*根据根据问卷id获取问卷信息*/
	@Query("from Questionnaire where id in (?1) order by createdDate desc")
	List<Questionnaire> getQuestionnaireList(List<String> list);
	
	/*根据根据答卷适用范围获取问卷id*/
	@Query("select id from Questionnaire  where   answerScope in (?1) and corpId=?2 and status =?3  ORDER BY createdDate DESC")
	List<String> getQuestionIdsByAnswerScope(List<String> scope, String corpId, String status);
	
	@Modifying
	@Query(nativeQuery = true,value = "update oa_questionnaire set status = ?2 where id = ?1")
	int updateQuStatus(String id, String status);

	/**
	 * 更新问卷 发送状态
	 * @param quesId 问卷id
	 * @param sendStatus 发送状态
	 * @return
	 */
	@Modifying
	@Query(nativeQuery = true,value = "update oa_questionnaire set send_status = ?2 where id = ?1")
	int updateSendStatus(String quesId, int sendStatus);

	/**
	 * 查询所有开启和未开启的问卷
	 * @return
	 */
	@Query("from Questionnaire  where  status = '1' or status = '2'")
	List<Questionnaire> getQuesNoClose();


	/**
	 * 更改问卷发布状态
	 * @param id
	 * @param publish
	 * @return
	 */
	@Modifying
	@Query(nativeQuery = true,value = "update oa_questionnaire set publish = ?2 where id = ?1")
	int updateQuPublish(String id, int publish);
	
	/**
	 * 更改问卷定时发布状态
	 * 
	 * @param id
	 * @param publish
	 * @param isTimingPublish
	 * @param date
	 * @return
	 */
	@Modifying
	@Query(nativeQuery = true, value = "update oa_questionnaire set publish = ?2, is_timing_publish = ?3, publish_date = ?4 where id = ?1")
	int updateQuTimingPublish(String id, int publish, int isTimingPublish, Date date);
	
	/**
	 * 停止问卷定时发布状态
	 * 
	 * @param id
	 * @param publish
	 * @param isTimingPublish
	 * @return
	 */
	@Modifying
	@Query(nativeQuery = true, value = "update oa_questionnaire set publish = ?2, is_timing_publish = ?3, publish_date = null where id = ?1")
	int stopQuTimingPublish(String id, int publish, int isTimingPublish);


	/**
	 * 问卷设计  1手机端设计   0pc端设计
	 * @param quesId
	 * @param status
	 * @return
	 */
	@Modifying
	@Query(nativeQuery = true,value = "update oa_questionnaire set is_mobile = ?3,topic_json=?2 where id = ?1")
	int updateMobile(String quesId,String topicJson, String status);
    
    /**
     * 更新超期问卷的状态publish
     * @return
     */
	@Transactional
    @Modifying
    @Query("update Questionnaire set publish = 2 where fn_date_format(?1,'yyyy-MM-dd HH:mm') >= fn_date_format(closeDate,'yyyy-MM-dd HH:mm')")
	int updateOverdueQuestionnaire(Date date);
	
	/**
	 * 定时发布问卷
	 * 
	 * @param date
	 * @return
	 */
	@Transactional
	@Modifying
	@Query("update Questionnaire set publish = 1 where id in (?1)")
	int publishQuestionnaire(List<String> idList);
	
	/**
	 * 查询所有待定时发布的问卷
	 * 
	 * @param date
	 * @return List<Questionnaire>
	 */
	@Query("from Questionnaire where publish = 0 and isTimingPublish = 1 and fn_date_format(?1,'yyyy-MM-dd HH:mm:ss') >= fn_date_format(publishDate,'yyyy-MM-dd HH:mm:ss')")
	List<Questionnaire> getPublishQuestionnaire(Date date);
}
